// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 创建MySQL连接池
const pool = mysql.createPool({
    host: '127.0.0.1', //MySQL服务器地址
    port: 3306, //MySQL服务器端口号
    user: 'root', //数据库用户的用户名
    password: '', //数据库用户密码
    database: 'natato', //数据库名称
    connectionLimit: 20, //最大连接数
    charset: 'utf8' //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
    extended: false
}));


// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
    origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));

// 首页home接口
server.get('/home', (req, res) => {
    // SQL语句以获取home数据
    let sql = 'SELECT * FROM home ORDER BY id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

// 关于我们aboutus接口
server.get('/aboutus', (req, res) => {
    // SQL语句以获取aboutus数据
    let sql = 'SELECT * FROM aboutus ORDER BY u_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//about下的reviews表接口
server.get('/reviews', (req, res) => {
    // SQL语句以获取reviews数据
    let sql = 'SELECT * FROM reviews ORDER BY re_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//教练团队coach接口
server.get('/coach', (req, res) => {
    // SQL语句以获取coach数据
    let sql = 'SELECT * FROM coach ORDER BY coac_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//课程介绍course接口
server.get('/course', (req, res) => {
    // SQL语句以获取course数据
    let sql = 'SELECT * FROM course ORDER BY cr_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//课程前言before_co接口
server.get('/before_co', (req, res) => {
    // SQL语句以获取course数据
    let sql = 'SELECT * FROM before_co ORDER BY co_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//场地展示area
server.get('/area', (req, res) => {
    // SQL语句以获取area数据
    let sql = 'SELECT * FROM area ORDER BY area_id';
    // 执行SQL语句
    pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//用户注册接口
server.post('/register', (req, res) => {
    // 获取用户名和密码信息
    let uname = req.body.uname;
    let upwd = req.body.upwd;
    // console.log(uname,upwd);
    //以uname为条件进行查找操作，以保证用户名的唯一性
    let sql = 'SELECT COUNT(uid) AS count FROM user WHERE uname=?';
    pool.query(sql, [uname], (error, results) => {
        if (error) throw error;
        let count = results[0].count;
        if (count == 0) {
            // 将用户的相关信息插入到数据表
            sql = 'INSERT user(uname,upwd) VALUES(?,?)';
            pool.query(sql, [uname, upwd], (error, results) => {
                if (error) throw error;
                res.send({ message: 'ok', code: 200 });
            })
        } else {
            res.send({ message: '用户名已存在', code: 201 });
        }
    });
});

// 用户登录接口
server.post('/login', (req, res) => {
    //获取用户名和密码信息
    let uname = req.body.uname;
    let upwd = req.body.upwd;
    // console.log(uname,upwd);
    // SQL语句
    let sql = 'SELECT uname,upwd FROM user WHERE uname=? AND upwd=?';
    pool.query(sql, [uname, upwd], (error, results) => {
        if (error) throw error;
        if (results.length == 0) { //登录失败
            res.send({ message: '用户名或密码错误', code: 201 });
        } else { //登录成功
            res.send({ message: 'ok', code: 200, result: results[0] });
        }
    });

});

//购买课程
server.post('/pay', (req, res) => {
    // 获取用户名和密码信息
    let ccc_name = req.body.coursename;
    let ccc_id = req.body.id;
    let username = req.body.username;
    //以username和ccc_name为条件进行查找操作，以保证用户名和课程唯一性
    let sql = 'SELECT COUNT(*) AS count FROM cr_choose WHERE username=? AND ccc_name=?';
    pool.query(sql, [username, ccc_name], (error, results) => {
        if (error) throw error;
        let count = results[0].count;
        if (count == 0) {
            // 将用户的相关信息插入到数据表
            sql = 'INSERT cr_choose(ccc_id,ccc_name,username) VALUES(?,?,?)';
            pool.query(sql, [ccc_id, ccc_name, username], (error, results) => {
                if (error) throw error;
                res.send({ message: 'ok', code: 200 });
            })
        } else {
            res.send({ message: '已购买过此课程', code: 201 });
        }
    })
});
//查询课程
server.post('/search',   (req,  res)  =>  {  
    let  username = req.body.username;   // SQL语句以获取用户信息数据
    let  sql = 'SELECT * FROM cr_choose WHERE USERNAME=?';   // 执行SQL语句
    pool.query(sql, [username], (error,  results)  =>  {    
        if  (error)  throw  error;    
        res.send({  message:   'ok',  code:  200,  results:  results  });  
    });
});

//客户预约课程插入数据库接口
server.post('/reserve', (req, res) => {
    // 获取用户名和密码信息
    let username = req.body.username; //用户名
    let rese_name = req.body.rese_name; //预约课程名称
    let rese_time = req.body.rese_time; //预约时间
    console.log(username, rese_name, rese_time);
    // 将用户的相关信息插入到数据表
    sql = 'INSERT reserve(username,rese_name,rese_time) VALUES(?,?,?)';
    pool.query(sql, [username, rese_name, rese_time], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
    })

});

//读取预约课程接口
server.post('/getreserve', (req, res) => {
    // 获取用户名信息
    let username = req.body.username; //获取用户名
    let sql = 'SELECT * FROM reserve WHERE username=?';
    // 执行SQL语句
    pool.query(sql, [username], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

// 指定服务器对象监听的端口号
server.listen(3000, () => {
    console.log('服务器开启成功...');
});